import requests
import json
import csv
import pymysql
import datetime


# ye = tree.xpath('//*[@id="page-fav"]/div[1]/div[2]/div[3]/ul[2]/span[1]/text()')

def json_num(id_lt, wtf, db, cursor):
    wtf.writerow(["--BV--", "--title--", "--jianjie--", "--shoucang--", "--bofang--", "--img_url--"])

    for i in id_lt:
        if "失效" in i.get("title"):
            print("失效")
        else:
            BV = i.get("bv_id")
            title = i.get("title")
            jianjie = i.get("intro")
            shoucang = i.get("cnt_info").get("collect")
            bofang = i.get("cnt_info").get("play")
            img_url = i.get("cover")
            new_time = datetime.datetime.now().strftime('%Y-%m-%d')
            qsql = "SELECT * FROM bilibili where BV = '%s'" % (BV)
            try:
                resut = cursor.execute(qsql)
            except:
                print("查询BV错误")
            if resut:
                print("已存在~~~")
            else:
                sql = "INSERT INTO bilibili(title,BV,bofang_num,create_time,newtime,jianjie,love_num,img_url)VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    title, BV, int(bofang),"2021-08-12", new_time, jianjie, int(shoucang), img_url)
                try:
                    cursor.execute(sql)
                    db.commit()
                except:
                    db.rollback()
            wtf.writerow([BV, title, jianjie, shoucang, bofang, img_url])


if __name__ == '__main__':
    fp = open("bvnum.csv", "w", encoding="utf-8", newline="")
    wtf = csv.writer(fp)
    db = pymysql.connect(
        host="localhost",
        user="root",
        password="123456",
        database="movie_cat"
    )
    cursor = db.cursor()
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36"
    }

    for i in range(1, 7):
        url = "https://api.bilibili.com/x/v3/fav/resource/list?media_id=1508679417&pn={}&ps=20&keyword=&order=view&type=0&tid=0&platform=web&jsonp=jsonp".format(
            str(i))
        response2 = requests.get(
            url=url,
            headers=headers)
        content2 = json.loads(response2.text)
        id_lt = content2.get("data").get("medias")
        json_num(id_lt, wtf, db, cursor)

    fp.close()
    db.close()
